package com.house.reportisy;

import com.house.dto.HouseDTO;
import com.house.entity.House;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Map;

/**
 * @author: GGP
 * @description: 房源DAO接口
 * @time: 2023-04-12 10:32
 **/
public interface HouseRepository extends JpaRepository<House,Integer>, JpaSpecificationExecutor<House> {

	/**
	 * 查看所有房源
	 * @param pageable
	 * @return
	 */
	@Query(value = "select h.id,h.house_title AS houseTitle,h.house_model AS houseModel,h.house_desc AS houseDesc,h.house_area AS houseArea,h.house_floor AS houseFloor,h.house_style AS houseStyle,h.house_type AS houseType,h.house_city AS houseCity,h.house_address AS houseAddress,h.community_name AS communityName,h.price,h.house_oriented AS houseOriented,u.u_name AS publisher,u.phone as publisherPhone,h.publish_time AS publishTime,h.house_image AS houseImage,h.score"+
			" from house as h join user as u on h.publisher_id=u.id"+
			" WHERE IF(:#{#houseDTO.houseCity}  is not null,h.house_city=:#{#houseDTO.houseCity},1=1)" +
			" and IF(:#{#houseDTO.houseOriented} is not null,h.house_oriented= :#{#houseDTO.houseOriented},1=1)" +
			" and IF(:#{#houseDTO.lowPrice} is not null and :#{#houseDTO.higPrice} is not null,h.price BETWEEN :#{#houseDTO.lowPrice} and :#{#houseDTO.higPrice},1=1)"+
			" and IF(:#{#houseDTO.lowArea} is not null and :#{#houseDTO.higArea} is not null,h.house_area BETWEEN :#{#houseDTO.lowArea} and :#{#houseDTO.higArea},1=1)"+
			" and IF(:#{#houseDTO.publisherId} is not null,h.publisher_id=:#{#houseDTO.publisherId},1=1) "+
			" and h.del_flag=0",nativeQuery = true)
	Page<Map<String,Object>> findAllHouse(@Param("houseDTO") HouseDTO houseDTO,Pageable pageable);

	/**
	 * 通过id查找-前台详情
	 * @return
	 */
	@Query(value = "select h.id,h.house_title AS houseTitle,h.house_model AS houseModel,h.house_desc AS houseDesc,h.house_area AS houseArea,h.house_floor AS houseFloor,h.house_style AS houseStyle,h.house_type AS houseType,h.house_city AS houseCity,h.house_address AS houseAddress,h.community_name AS communityName,h.price,h.house_oriented AS houseOriented,u.u_name AS publisher,u.phone as publisherPhone,h.publish_time AS publishTime,h.house_image AS houseImage,h.score"+
			" from house as h join user as u on h.publisher_id=u.id"+
			" where h.id=:houseId",nativeQuery = true)
	Map<String,Object> findHouseById(@Param("houseId") Integer houseId);
}
